﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting

Partial Class Reportes_RPTCruce
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
        End If


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
        'generarGrafica()
    End Sub
    Sub cargardatos()
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruce", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@codigoProyecto", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd.Parameters.AddWithValue("@campo1", ddlCampo1.SelectedValue)
                cmd.Parameters.AddWithValue("@campo2", If(ddlCampo2.SelectedValue = "0", DBNull.Value, ddlCampo2.SelectedValue))
                cmd.Parameters.AddWithValue("@campo3", If(ddlCampo3.SelectedValue = "0", DBNull.Value, ddlCampo3.SelectedValue))
                cmd.Parameters.AddWithValue("@filtro1", ddlfiltro1.SelectedValue)
                cmd.Parameters.AddWithValue("@filtro2", ddlfiltro2.SelectedValue)
                cmd.Parameters.AddWithValue("@filtro3", ddlfiltro3.SelectedValue)

                grdResultado.DataSource = cmd.ExecuteReader
                grdResultado.DataBind()
                Dim titulo As String = "<h2> Reporte de cruce de variables<br/>Fecha:" & Date.Now.ToShortDateString & "<h2>"
                lblTitulo.Text = titulo
            End Using

            cnn.Close()

        End Using

        'Formatear columnas de datos
        'Dim columnas As Integer = grdResultado.AutoGenerateColumns
        'Dim columnas As Integer = 6
        'grdResultado.Columns(columnas - 1).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 2).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 3).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 4).ItemStyle.HorizontalAlign = HorizontalAlign.Right




    End Sub
    Protected Sub btnExpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles btnExpt.Click

    End Sub

    Protected Sub exportarAExcel()


        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As StringWriter = New StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page

        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)

        'form.Controls.Add(tabla)
        form.Controls.Add(Me.grdResultado)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=Reporte.xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btnExp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExp.Click
        cargardatos()
        exportarAExcel()
    End Sub

    Sub llenarCombos()
        Dim u As New Web_Usuario
        Using h As New Helper
            u = h.GetUsuario(Context.User.Identity.Name)
        End Using
        Using bd As New BdHogaresDataContext
            Dim a = From i In bd.Web_Proyectos
                    Where (i.web_Operadore.idOperador = u.IdOperador) Or IsNothing(u.IdOperador)
                    Select i.idProyecto, i.CodigoProyecto
            ddlProyectos.DataSource = a
            ddlProyectos.DataValueField = "CodigoProyecto"
            ddlProyectos.DataTextField = "CodigoProyecto"
            ddlProyectos.DataBind()
            ddlProyectos.Items.Insert(0, New ListItem("Todos", "0"))

        End Using
        Using bd As New bdReportesDataContext

            ddlMunicipio.DataSource = bd.usp_MunicipiosEstrategia(Context.User.Identity.Name)
            ddlMunicipio.DataValueField = "codMunicipio"
            ddlMunicipio.DataTextField = "Municipio"
            ddlMunicipio.DataBind()
            ddlMunicipio.Items.Insert(0, New ListItem("Todos", "0"))
        End Using


        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()
            LlenarItems(ddlCampo1, "usp_RPTCruce", cnn)
            'ddlCampo1.Items.Insert(0, New ListItem("Sin variable", "Todos"))
            LlenarItems(ddlCampo2, "usp_RPTCruce", cnn)
            ddlCampo2.Items.Insert(0, New ListItem("Sin variable", "0"))
            LlenarItems(ddlCampo3, "usp_RPTCruce", cnn)
            ddlCampo3.Items.Insert(0, New ListItem("Sin variable", "0"))
            cnn.Close()
        End Using
    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub

    Protected Sub ddlCampo1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo1.SelectedIndexChanged
        Cargarfiltros(ddlCampo1, ddlfiltro1)
    End Sub

    Sub Cargarfiltros(ByRef campo As DropDownList, ByRef filtro As DropDownList)
        If campo.SelectedValue <> "0" Then

            Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
                cnn.Open()

                Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruceFiltro", cnn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@campo1", campo.SelectedValue)
                    filtro.DataSource = cmd.ExecuteReader
                    filtro.DataTextField = "detalle"
                    filtro.DataValueField = "valor"
                    filtro.DataBind()
                    filtro.Items.Insert(0, New ListItem("Todos", "Todos"))
                End Using

                cnn.Close()

            End Using
        Else
            filtro.Items.Clear()
        End If

    End Sub

    Protected Sub ddlCampo2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo2.SelectedIndexChanged
        Cargarfiltros(ddlCampo2, ddlfiltro2)
    End Sub

    Protected Sub ddlCampo3_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo3.SelectedIndexChanged
        Cargarfiltros(ddlCampo3, ddlfiltro3)
    End Sub
End Class
